package com.tron.jdbc;

import java.sql.*;

public class MySqlStart {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Connection connection = getConnection();
//        int update = createTable(statement);
//        int insert = insertData(statement);
//        selectData(connection);
//        prepareSelectData(connection, "2");
//        showTables(connection);
        showTable(connection,"student");


    }

    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver"); // 加载数据库驱动
        String url = "jdbc:mysql://192.168.192.128:3306/mydata?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        String userName = "root";
        String password = "123456";
        return DriverManager.getConnection(url, userName, password);
    }

    public static int createTable(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        int update = statement.executeUpdate("create table if not exists student(id varchar(64), name varchar(255),age int,primary key(id))");
        statement.close();
        connection.close();
        return update;
    }

    public static int insertData(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        int update = statement.executeUpdate("insert into student values ('2','tom',100)");
        statement.close();
        connection.close();
        return update;
    }

    public static void selectData(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from student");
        while (resultSet.next()) {
            String id = resultSet.getString(1);
            String name = resultSet.getString(2);
            int age = resultSet.getInt(3);
            System.out.printf("%s %s %s\n", id, name, age);
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    public static void prepareSelectData(Connection connection, String userId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("select * from student where id = ?");
        preparedStatement.setString(1, userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString(1);
            String name = resultSet.getString(2);
            int age = resultSet.getInt(3);
            System.out.printf("%s %s %s\n", id, name, age);
        }
        resultSet.close();
        connection.close();
    }

    public static void showTables(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getTables(null, null, null, new String[]{"TABLE"});
        System.out.println("库名      模式       表名      表类型     注释");
        while (resultSet.next()) {
            String string1 = resultSet.getString(1);
            String string2 = resultSet.getString(2);
            String string3 = resultSet.getString(3);
            String string4 = resultSet.getString(4);
            String string5 = resultSet.getString(5);
            System.out.printf("%s %10s %10s %10s %10s\n",string1,string2,string3,string4,string5);
        }
        connection.close();
    }

    public static void showTable(Connection connection,String tableName) throws SQLException {
        String sql = "select * from " + tableName;
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        ResultSetMetaData metaData = resultSet.getMetaData();
        System.out.println("字段名   字段类型  字段长度  对应类型");
        for (int i = 1; i < metaData.getColumnCount(); i++) {
            String columnName = metaData.getColumnLabel(i);
            String columnTypeName = metaData.getColumnTypeName(i);
            int columnDisplaySize = metaData.getColumnDisplaySize(i);
            String columnClassName = metaData.getColumnClassName(i);
            System.out.printf("%-8s %-8s %-8s %s \n",columnName,columnTypeName,columnDisplaySize,columnClassName);
        }
        resultSet.close();
        connection.close();
    }

    public static void commitByMyself(Connection connection) throws SQLException {
        connection.setAutoCommit(false);
        Statement statement = connection.createStatement();
        // .......
        // .......
        if (true) {
            connection.commit();
        }else {
            connection.rollback();
        }
        connection.close();
    }
}
